﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using AlarmManagePlatform.Model.DB;

namespace AlarmManagePlatform.DataAccess
{
    internal class tb_User
    {
        internal Model.DB.tb_User CheckUserInfo(string userName, string passWord)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            try
            {
                Model.DB.tb_User _Result = null;
                DataTable _DataTable;
                string _SqlScript = string.Format("select * from tb_User where userName = '{0}' and passWord = '{1}';", userName, passWord);
                _SqlDb = Static.Methods.OpenDataBase();
                _DataTable = _SqlDb.RunSqlScript(_SqlScript);


                if (_DataTable.Rows.Count > 0)
                {
                    _Result = DataTableToList(_DataTable)[0];
                }
                else
                {
                    _Result = null;
                }

                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取用户信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal bool CheckUserNameIsExist(string userName)
        {
            Relations.DBUtility.SqlServer.SqlDb sqlDb = null;
            try
            {
                string sqlScript;
                sqlScript = string.Format("Select * From tb_User Where userName = '{0}';", userName);
                sqlDb = Static.Methods.OpenDataBase();
                if (sqlDb.RunSqlScript(sqlScript).Rows.Count > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception("检查账户是否存在失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (sqlDb != null)
                    Static.Methods.CloseDataBase(sqlDb);
            }
        }

        internal Model.DB.tb_User GetForUserId(int userId)
        {
            Relations.DBUtility.SqlServer.SqlDb sqlDb = null;
            try
            {
                Model.DB.tb_User result = null;
                DataTable dataTable;
                string sqlScript = string.Format("select userId,userName,passWord,roleId,createTime from tb_User where userId = {0};", userId);
                sqlDb = Static.Methods.OpenDataBase();
                dataTable = sqlDb.RunSqlScript(sqlScript);
                if (dataTable.Rows.Count > 0)
                {
                    result = new Model.DB.tb_User();
                    result.userId = int.Parse(dataTable.Rows[0]["userId"].ToString());
                    result.userName = dataTable.Rows[0]["userName"].ToString();
                    result.passWord = dataTable.Rows[0]["passWord"].ToString();
                    result.roleId = int.Parse(dataTable.Rows[0]["roleId"].ToString());
                }
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取用户信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (sqlDb != null)
                    Static.Methods.CloseDataBase(sqlDb);
            }
        }

        internal List<Model.DB.tb_User> GetUserInfoList(Dictionary<string, string> logisticList)
        {
            List<Model.DB.tb_User> _Result;
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            string _SqlScript = string.Empty;
            DataTable _DataTable;
            try
            {
                _SqlScript = string.Format("Select * from tb_User Where userId > 0 ");

                if (!string.IsNullOrEmpty(logisticList["userName"]))
                {
                    _SqlScript += $"     And userName like '%{logisticList["userName"]}%'";
                }

                if (!string.IsNullOrEmpty(logisticList["roleId"]))
                {
                    _SqlScript += $"     And roleId = {logisticList["roleId"]}";
                }

                _SqlScript += "     Order By roleId Desc , userId Asc";

                _SqlDb = Static.Methods.OpenDataBase();
                _DataTable = _SqlDb.RunSqlScript(_SqlScript);

                _Result = DataTableToList(_DataTable);

                return _Result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取用户信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal void DeleteForUserID(List<string> indexList)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            string _TaskIds = string.Empty;
            string _SqlScript = string.Empty;
            try
            {
                _SqlDb = Static.Methods.OpenDataBase();
                _TaskIds = Static.Methods.CombiningStrings2<string>(indexList);

                _SqlScript = "Delete From tb_User";
                _SqlScript += $" Where userId in ( {_TaskIds} )";

                _SqlDb.UpdateSqlScript(_SqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("删除用户失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }


        internal void SaveUserInfo(Model.DB.tb_User userInfo)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            string _SqlScript = string.Empty;
            try
            {
                _SqlScript += "     Insert into tb_User ([userName],[passWord],[roleId],[createTime])";
                _SqlScript += $"    Values('{userInfo.userName}','{userInfo.passWord}',{userInfo.roleId},'{DateTime.Now.ToString(Static.ParaInfo.DateFormatLong)}')";

                _SqlDb = Static.Methods.OpenDataBase();
                _SqlDb.UpdateSqlScript(_SqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("保存用户信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }


        /// <summary>
        /// 获取所有用户信息
        /// </summary>
        /// <returns></returns>
        internal Model.DB.tb_User[] getAll()
        {
            Relations.DBUtility.SqlServer.SqlDb sqlDb = null;
            try
            {
                Model.DB.tb_User[] result;
                DataTable dataTable;
                string sqlScript = "select userId,userName,roleId,tonken from tb_User order by userId;";
                sqlDb = Static.Methods.OpenDataBase();
                dataTable = sqlDb.RunSqlScript(sqlScript);
                result = new Model.DB.tb_User[dataTable.Rows.Count];
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    result[i] = new Model.DB.tb_User();
                    result[i].userId = int.Parse(dataTable.Rows[i]["userId"].ToString());
                    result[i].userName = dataTable.Rows[i]["userName"].ToString();
                    result[i].roleId = int.Parse(dataTable.Rows[i]["roleId"].ToString());
                }
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception("获取所有用户信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (sqlDb != null)
                    Static.Methods.CloseDataBase(sqlDb);
            }
        }

        internal void UpdateUserInfo(Model.DB.tb_User userInfo)
        {
            Relations.DBUtility.SqlServer.SqlDb _SqlDb = null;
            string _SqlScript = string.Empty;
            try
            {
                _SqlScript = $"  Update tb_User Set[passWord] = '{userInfo.passWord}',[roleId] = {userInfo.roleId}";
                _SqlScript += $"  Where [userId] = {userInfo.userId}";

                _SqlDb = Static.Methods.OpenDataBase();
                _SqlDb.UpdateSqlScript(_SqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("修改用户信息失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (_SqlDb != null)
                    Static.Methods.CloseDataBase(_SqlDb);
            }
        }

        internal void updateTonken(int userId, string tonken)
        {
            Relations.DBUtility.SqlServer.SqlDb sqlDb = null;
            try
            {
                string sqlScript = string.Format("update tb_User set Tonken = '{0}' where userId = {1};", tonken, userId);
                sqlDb = Static.Methods.OpenDataBase();
                sqlDb.UpdateSqlScript(sqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("更新用户Tonken失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (sqlDb != null)
                    Static.Methods.CloseDataBase(sqlDb);
            }
        }

        internal bool checkTonken(string tonken)
        {
            Relations.DBUtility.SqlServer.SqlDb sqlDb = null;
            try
            {
                string sqlScript = string.Format("select userId from tb_User where Tonken = '{0}';", tonken);
                DataTable tempDT;
                sqlDb = Static.Methods.OpenDataBase();
                tempDT = sqlDb.RunSqlScript(sqlScript);
                if (tempDT.Rows.Count > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception("验证Tonken失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (sqlDb != null)
                    Static.Methods.CloseDataBase(sqlDb);
            }
        }

        internal void updatePassword(string userId, string newPassword)
        {
            Relations.DBUtility.SqlServer.SqlDb sqlDb = null;
            try
            {
                string sqlScript = string.Format("update tb_User set passWord = '{0}'  where userId = '{1}';", newPassword, userId);
                sqlDb = Static.Methods.OpenDataBase();
                sqlDb.UpdateSqlScript(sqlScript);
            }
            catch (Exception ex)
            {
                throw new Exception("修改密码失败，错误信息：" + ex.Message);
            }
            finally
            {
                if (sqlDb != null)
                    Static.Methods.CloseDataBase(sqlDb);
            }
        }

        private List<Model.DB.tb_User> DataTableToList(DataTable dataTable)
        {
            List<Model.DB.tb_User> _List;
            Model.DB.tb_User _TempUser;

            try
            {
                _List = new List<Model.DB.tb_User>();

                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    _TempUser = null;
                    _TempUser = new Model.DB.tb_User();
                    _TempUser.userId = Convert.ToInt32(dataTable.Rows[i]["userId"].ToString());
                    _TempUser.userName = dataTable.Rows[i]["userName"].ToString();
                    _TempUser.passWord = dataTable.Rows[i]["passWord"].ToString();
                    _TempUser.roleId = Convert.ToInt32(dataTable.Rows[i]["roleId"].ToString());
                    _TempUser.roleName = new DataAccess.tb_RoleInfo().GetRoleInfo(_TempUser.roleId.ToString()).Name;
                    _TempUser.tonken = dataTable.Rows[i]["tonken"].ToString();

                    if (!string.IsNullOrEmpty(dataTable.Rows[i]["updateTime"].ToString()))
                        _TempUser.updateTime = DateTime.Parse(dataTable.Rows[i]["updateTime"].ToString()).ToString(Static.ParaInfo.DateFormatLong);

                    if (!string.IsNullOrEmpty(dataTable.Rows[i]["createTime"].ToString()))
                        _TempUser.createTime = DateTime.Parse(dataTable.Rows[i]["createTime"].ToString()).ToString(Static.ParaInfo.DateFormatLong);

                    _List.Add(_TempUser);
                }

                return _List;
            }
            catch (Exception ex)
            {
                throw new Exception("将Datatable转换成List<Model.DB.tb_User>有异常：" + ex.ToString());
            }
        }
    }
}